package top.went.db.dao;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import top.went.pojo.ApprovalEntity;

import java.util.List;

public interface ApprovalDao extends CrudRepository<ApprovalEntity, Long> {

    /**
     * 查找所有审批记录
     *
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT a.APPROVAL_ID,a.APPROVAL_USER_ID,a.APPROVAL_APPLY_TIME,a.approval_result," +
            "a.APPROVAL_CONTENT,a.APPROVAL_ORDER_ID,a.APPROVAL_STATUS,a.APPROVAL_RECEIVER_ID,a.APPROVAL_TIME," +
            "a.APPROVAL_TYPE,(CASE WHEN a.APPROVAL_TYPE='订单' THEN " +
            "(SELECT o.ORDER_TITLE FROM TB_ORDER o WHERE o.ORDER_ID=a.APPROVAL_ORDER_ID and o.LOGIN_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='报价单' THEN (SELECT q.QUOTE_THEME FROM TB_QUOTE q WHERE q.QUOTE_ID=a.APPROVAL_ORDER_ID and q.IS_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='采购单' THEN (SELECT p.PUR_THEME FROM TB_PURCHASE p WHERE p.PUR_ID=a.APPROVAL_ORDER_ID and p.MAGIC_DELETE=0) " +
            "END) APPROVAL_title from TB_APPROVAL a order by ?#{#pageable}", countQuery = "SELECT count(*) from TB_APPROVAL")
    public List<ApprovalEntity> findAllApps(Pageable pageable);

    /**
     * 根据申请人查找审批记录
     *
     * @param userId
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT a.APPROVAL_ID,a.APPROVAL_USER_ID,a.APPROVAL_APPLY_TIME,a.approval_result," +
            "a.APPROVAL_CONTENT,a.APPROVAL_ORDER_ID,a.APPROVAL_STATUS,a.APPROVAL_RECEIVER_ID,a.APPROVAL_TIME," +
            "a.APPROVAL_TYPE,(CASE WHEN a.APPROVAL_TYPE='订单' THEN " +
            "(SELECT o.ORDER_TITLE FROM TB_ORDER o WHERE o.ORDER_ID=a.APPROVAL_ORDER_ID and o.LOGIN_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='报价单' THEN (SELECT q.QUOTE_THEME FROM TB_QUOTE q WHERE q.QUOTE_ID=a.APPROVAL_ORDER_ID and q.IS_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='采购单' THEN (SELECT p.PUR_THEME FROM TB_PURCHASE p WHERE p.PUR_ID=a.APPROVAL_ORDER_ID and p.MAGIC_DELETE=0) " +
            "END) APPROVAL_title from TB_APPROVAL a where a.APPROVAL_USER_ID=?1 order by ?#{#pageable}",
            countQuery = "SELECT count(*) from TB_APPROVAL where a.APPROVAL_USER_ID=?1")
    public List<ApprovalEntity> findAllByUser(Long userId, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT count(*) from TB_APPROVAL a where a.APPROVAL_USER_ID=?1")
    public long countAllByUser(Long userId);

    /**
     * 根据审批类型查找审批记录
     *
     * @param approvalType
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT a.APPROVAL_ID,a.APPROVAL_USER_ID,a.APPROVAL_APPLY_TIME,a.approval_result," +
            "a.APPROVAL_CONTENT,a.APPROVAL_ORDER_ID,a.APPROVAL_STATUS,a.APPROVAL_RECEIVER_ID,a.APPROVAL_TIME," +
            "a.APPROVAL_TYPE,(CASE WHEN a.APPROVAL_TYPE='订单' THEN " +
            "(SELECT o.ORDER_TITLE FROM TB_ORDER o WHERE o.ORDER_ID=a.APPROVAL_ORDER_ID and o.LOGIN_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='报价单' THEN (SELECT q.QUOTE_THEME FROM TB_QUOTE q WHERE q.QUOTE_ID=a.APPROVAL_ORDER_ID and q.IS_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='采购单' THEN (SELECT p.PUR_THEME FROM TB_PURCHASE p WHERE p.PUR_ID=a.APPROVAL_ORDER_ID and p.MAGIC_DELETE=0) " +
            "END) APPROVAL_title from TB_APPROVAL a where a.APPROVAL_TYPE=?1 order by ?#{#pageable}",
            countQuery = "SELECT count(*) from TB_APPROVAL where a.APPROVAL_TYPE=?1")
    public List<ApprovalEntity> findAllByApprovalType(String approvalType, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT count(*) from TB_APPROVAL a where a.APPROVAL_TYPE=?1")
    public long countAllByApprovalType(String approvalType);

    /**
     * 根据申请人和类型查找审批记录
     *
     * @param userId
     * @param approvalType
     * @param pageable
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT a.APPROVAL_ID,a.APPROVAL_USER_ID,a.APPROVAL_APPLY_TIME,a.approval_result," +
            "a.APPROVAL_CONTENT,a.APPROVAL_ORDER_ID,a.APPROVAL_STATUS,a.APPROVAL_RECEIVER_ID,a.APPROVAL_TIME," +
            "a.APPROVAL_TYPE,(CASE WHEN a.APPROVAL_TYPE='订单' THEN " +
            "(SELECT o.ORDER_TITLE FROM TB_ORDER o WHERE o.ORDER_ID=a.APPROVAL_ORDER_ID and o.LOGIN_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='报价单' THEN (SELECT q.QUOTE_THEME FROM TB_QUOTE q WHERE q.QUOTE_ID=a.APPROVAL_ORDER_ID and q.IS_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='采购单' THEN (SELECT p.PUR_THEME FROM TB_PURCHASE p WHERE p.PUR_ID=a.APPROVAL_ORDER_ID and p.MAGIC_DELETE=0) " +
            "END) APPROVAL_title from TB_APPROVAL a where a.APPROVAL_USER_ID=?1 and a.APPROVAL_TYPE=?2 order by ?#{#pageable}",
            countQuery = "SELECT count(*) from TB_APPROVAL where a.APPROVAL_USER_ID=?1 and a.APPROVAL_TYPE=?2")
    public List<ApprovalEntity> findAllByUserAndType(Long userId, String approvalType, Pageable pageable);

    @Query(nativeQuery = true, value = "SELECT count(*) from TB_APPROVAL a where a.APPROVAL_USER_ID=?1 and a.APPROVAL_TYPE=?2")
    public long countAllByUserAndType(Long userId, String approvalType);

    /**
     * 加载审批
     *
     * @param approvalId
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT a.APPROVAL_ID,a.APPROVAL_USER_ID,a.APPROVAL_APPLY_TIME,a.approval_result," +
            "a.APPROVAL_CONTENT,a.APPROVAL_ORDER_ID,a.APPROVAL_STATUS,a.APPROVAL_RECEIVER_ID,a.APPROVAL_TIME," +
            "a.APPROVAL_TYPE,(CASE WHEN a.APPROVAL_TYPE='订单' THEN " +
            "(SELECT o.ORDER_TITLE FROM TB_ORDER o WHERE o.ORDER_ID=a.APPROVAL_ORDER_ID and o.LOGIN_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='报价单' THEN (SELECT q.QUOTE_THEME FROM TB_QUOTE q WHERE q.QUOTE_ID=a.APPROVAL_ORDER_ID and q.IS_DELETE=0) " +
            "WHEN a.APPROVAL_TYPE='采购单' THEN (SELECT p.PUR_THEME FROM TB_PURCHASE p WHERE p.PUR_ID=a.APPROVAL_ORDER_ID and p.MAGIC_DELETE=0) " +
            "END) APPROVAL_title from TB_APPROVAL a where a.APPROVAL_ID=?1")
    public ApprovalEntity loadOne(Long approvalId);

    @Query("from ApprovalEntity a where  (a.approvalStatus = '1' or a.approvalStatus = '2') and a.approvalOrderId = ?1 and a.approvalType = ?2")
    ApprovalEntity loadOne(Integer id,String type);

    @Query("from ApprovalEntity a where  a.approvalOrderId = ?1 and a.approvalType = ?2 order by a.approvalId asc")
    List<ApprovalEntity> findById(Integer id, String type);
}
